/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.cheapflix.dao;

import com.cheapflix.beans.DVD;
import com.cheapflix.beans.User;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;

/**
 * DAO class with Recommendation functionalities.
 * @author Group B
 */
public class RecommendedDAO extends AbstractDAO {

    private static final Logger logger = Logger.getLogger(RecommendedDAO.class);
    //default constructor to make Spring happy :)

    public RecommendedDAO() {
    }

    @Autowired
    public RecommendedDAO(DataSource ds) {
        super.init(ds);
    }

    /**
     * Get the number of times a specific flick was recommended to a user.
     * @param myid
     * @param flickid
     * @return 
     */
    public int getNumberTimesFlickRecommendedToMe(int myid, int flickid) {
        String sql = "select count(*) from user_dvd_reccomend  where reccomendee_id = " + myid + " and dvd_id = " + flickid;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = getDataSource().getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                return rs.getInt(1);
            } else {
                return -1;
            }
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            return -1;
        } finally {
            close(conn, stmt, null);
        }
    }

    /**
     * Get all movies that were recommended to a user.
     * @param meId
     * @return 
     */
    public Set<DVD> getMoviesRecommendedToMe(int meId) {
        Set<DVD> movies = new LinkedHashSet< DVD>();
        String sql = "select b.*,a.reccomendor_id from user_dvd_reccomend a, dvd b where a.reccomendee_id =" + meId + " and a.dvd_id=b.id group by b.id";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = getDataSource().getConnection();
            stmt = conn.createStatement();
            logger.info(">sql: " + sql);
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                DVD dvd = DVDDao.ResultSetToDVD(rs);
                movies.add(dvd);
            }
            return movies;
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            return null;
        } finally {
            close(conn, stmt, null);
        }
    }

    /**
     * Recommend a DVD to a user
     * @param meId me  
     * @param dvdId dvd to reocmmend
     * @param friendId friend to reccomend DVD to.
     * @return 
     */
    public boolean recommendDVDToUser(int meId, int dvdId, int friendId) {
        String sql = "insert into user_dvd_reccomend ( reccomendor_id,reccomendee_id,dvd_id) values (" + meId + "," + friendId + "," + dvdId + ")";
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = getDataSource().getConnection();
            stmt = conn.createStatement();
            logger.info(">sql: " + sql);
            int numupdated = stmt.executeUpdate(sql);
            if (numupdated == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            return false;
        } finally {
            close(conn, stmt, null);
        }
    }

    /**
     * Get all the friends that have recommended a particular DVD to me.
     * @param myId me
     * @param dvdid DVD that has been recommended to me.
     * @return 
     */
    public List<User> getFriendsWhoRecommendedFlickToMe(int myId, int dvdid) {
        String sql = "select a.* from user a, user_dvd_reccomend b where b.reccomendee_id = " + myId + " and b.dvd_id=" + dvdid + " and a.id=b.reccomendor_id;";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<User> users=new ArrayList<User>();
        try {
            conn = getDataSource().getConnection();
            stmt = conn.createStatement();
            
            logger.info(">sql: " + sql);
            rs =stmt.executeQuery(sql);
            while(rs.next()){
                User user=UserDAO.getUser(rs);
                users.add(user);
            }
            return users;
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            return null;
        } finally {
            close(conn, stmt, null);
        }

    }
}
